﻿using System;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Data.EntityClient;
using System.ComponentModel;
using System.Xml.Serialization;
using System.Runtime.Serialization;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;

namespace PUZZLEgestServer.DAL
{
    public enum EntityStateType
        {
            Unchanged = 0,
            Created = 1,
            Updated = 2,
            Deleted = 3
        }

    public partial class Entities : ObjectContext
    {
       


        public static string SuperAdminConnectionStringEntity
        {
            get
            {
                string user = Util.Globals.ReadRegistryKey("user", true);
                string password = Util.Globals.ReadRegistryKey("password", true);
                string server = ConfigurationManager.AppSettings.Get("DBServer");
                string catalog = ConfigurationManager.AppSettings.Get("DBCatalog");

                string connectionString = new System.Data.EntityClient.EntityConnectionStringBuilder
                {
                    Metadata = "res://*",
                    Provider = "System.Data.SqlClient",
                    ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder
                    {
                        InitialCatalog = catalog,
                        DataSource = server,
                        IntegratedSecurity = false,
                        UserID = user,                 // User ID such as "sa"
                        Password = password,               // hide the password
                    }.ConnectionString
                }.ConnectionString;

                return connectionString;
            }
        }

        public static string UserConnectionStringEntity(string user, string password)
        {
            string server = ConfigurationManager.AppSettings.Get("DBServer");
            string catalog = ConfigurationManager.AppSettings.Get("DBCatalog");

            string connectionString = new System.Data.EntityClient.EntityConnectionStringBuilder
            {
                Metadata = "res://*",
                Provider = "System.Data.SqlClient",
                ProviderConnectionString = new System.Data.SqlClient.SqlConnectionStringBuilder
                {
                    InitialCatalog = catalog,
                    DataSource = server,
                    IntegratedSecurity = false,
                    UserID = user,                 // User ID such as "sa"
                    Password = password,               // hide the password
                }.ConnectionString
            }.ConnectionString;

            return connectionString;
        }

        /// <summary>
        /// Create Update Delete entity object
        /// </summary>
        /// <param name="entities"></param>
        /// <param name="entitySetName"></param>
        /// <param name="entity"></param>
        public static void CUD(DAL.Entities entities, string entitySetName, object entity)
        {
            if (entity.GetType().GetProperty("EntityInternalState") != null)
            {
                DAL.EntityStateType state = (DAL.EntityStateType)entity.GetType().GetProperty("EntityInternalState").GetValue(entity, null);

                switch (state)
                {
                    case EntityStateType.Created:
                        entities.AddObject(entitySetName, entity);
                        break;
                    case EntityStateType.Deleted:
                        entities.DeleteObject(entity);
                        break;
                    case EntityStateType.Updated:
                        entities.AttachTo(entitySetName, entity);
                        break;
                }
            }
            else
            {
                int id = (int)entity.GetType().GetProperty("id").GetValue(entity, null);
                if (id == 0)
                    entities.AddObject(entitySetName, entity);
                else if (id > 0)
                    entities.AttachTo(entitySetName, entity);
                else
                {
                    entity.GetType().GetProperty("id").SetValue(entity, -1 * id, null);
                    entities.DeleteObject(entity);
                }
            }
        }

        public enum ModulesType
        { 
            UserAdministration = 1, 
            FrontEnd = 2
        }

        public void DropSQLUser(string userName)
        {
            if (string.IsNullOrEmpty(userName))
                throw new Exception("User name is mandatory");

            if (userName.Contains(" "))
                throw new Exception("Invalid user name");

            string catalog = ConfigurationManager.AppSettings.Get("DBCatalog");

            try
            {
                StringBuilder sb = new System.Text.StringBuilder();

                sb.Append("USE [master];\n");
                sb.Append("DROP LOGIN [" + userName + "];\n");

                this.ExecuteStoreCommand(sb.ToString());

                sb = new System.Text.StringBuilder();

                sb.Append("USE [" + catalog + "];\n");
                sb.Append("DROP SCHEMA [" + userName + "];\n");
                sb.Append("USE [" + catalog + "];\n");
                sb.Append("DROP USER [" + userName + "];\n");

                this.ExecuteStoreCommand(sb.ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public enum SQLUserRight
        {
            SELECT,
            ALTER,
            CONTROL,
            DELETE,
            INSERT,
            REFERENCES,
            TAKE_OWNERSHIP,
            UPDATE,
            VIEW_CHANGE_TRACKING,
            VIEW_DEFINITION
        }

        public void AddRoleMember(string userName, string roleName)
        {
            if (string.IsNullOrEmpty(userName))
                throw new Exception("User name is mandatory");

            if (string.IsNullOrEmpty(roleName))
                throw new Exception("Role name is mandatory");

            string catalog = ConfigurationManager.AppSettings.Get("DBCatalog");

            try
            {
                   StringBuilder sb = new System.Text.StringBuilder();
                   sb.Append("USE [" + catalog + "];\n");
                   sb.Append("EXEC sp_addrolemember N'" + roleName + "', N'" + userName + "';");

                   this.ExecuteStoreCommand(sb.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DropRoleMember(string userName, string roleName)
        {
            if (string.IsNullOrEmpty(userName))
                throw new Exception("User name is mandatory");

            if (string.IsNullOrEmpty(roleName))
                throw new Exception("Role name is mandatory");

            string catalog = ConfigurationManager.AppSettings.Get("DBCatalog");

            try
            {
                StringBuilder sb = new System.Text.StringBuilder();
                sb.Append("USE [" + catalog + "];\n");
                sb.Append("EXEC sp_droprolemember N'" + roleName + "', N'" + userName + "';");

                this.ExecuteStoreCommand(sb.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        
        public void CreateSQLUser(string userName, string userPassword)
        {
            if (string.IsNullOrEmpty(userName))
                throw new Exception("User name is mandatory");

            if (userName.Contains(" "))
                throw new Exception("Invalid user name");

            if (string.IsNullOrEmpty(userPassword))
                throw new Exception("User password is mandatory");

            string catalog = ConfigurationManager.AppSettings.Get("DBCatalog");

            try
            {

                StringBuilder sb = new System.Text.StringBuilder();
                sb.Append("USE [master];\n");

                sb.Append("CREATE LOGIN [" + userName + "] WITH PASSWORD=N'" + userPassword + "', DEFAULT_DATABASE=[" + catalog + "], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;\n");

                sb.Append("USE [" + catalog + "];\n");

                sb.Append("CREATE USER [" + userName + "] FOR LOGIN [" + userName + "];\n");

                //sb.Append("USE [" + catalog + "];\n");

                //sb.Append("ALTER USER [" + userName + "] WITH DEFAULT_SCHEMA=[" + userName + "];\n");

                //sb.Append("use [" + catalog + "];\n");

                this.ExecuteStoreCommand(sb.ToString());

                //sb = new System.Text.StringBuilder();
                //sb.Append("CREATE SCHEMA [" + userName + "] AUTHORIZATION [" + userName + "];\n");

                //this.ExecuteStoreCommand(sb.ToString());


               

                //sb.Append("use [" + catalog + "];\n");

                //sb.Append("GRANT SELECT ON [dbo].[Modules] TO [" + userName + "];\n");

                //sb.Append("use [" + catalog + "];\n");
                //sb.Append("DENY ALTER ON [dbo].[Modules] TO [" + userName + "];\n");

                //sb.Append("use [" + catalog + "];\n");

                //sb.Append("GRANT CONTROL ON [dbo].[Modules] TO [" + userName + "];\n");

                //sb.Append("use [" + catalog + "];\n");
                //sb.Append("DENY DELETE ON [dbo].[Modules] TO [" + userName + "];\n");

                //sb.Append("use [" + catalog + "];\n");
                //sb.Append("DENY INSERT ON [dbo].[Modules] TO [" + userName + "];\n");

                //sb.Append("use [" + catalog + "];\n");

                //sb.Append("GRANT REFERENCES ON [dbo].[Modules] TO [" + userName + "];\n");

                //sb.Append("use [" + catalog + "];\n");

                //sb.Append("GRANT TAKE OWNERSHIP ON [dbo].[Modules] TO [" + userName + "];\n");

                //sb.Append("use [" + catalog + "];\n");

                //sb.Append("DENY UPDATE ON [dbo].[Modules] TO [" + userName + "];\n");

                //sb.Append("use [" + catalog + "];\n");

                //sb.Append("GRANT VIEW CHANGE TRACKING ON [dbo].[Modules] TO [" + userName + "];\n");

                //sb.Append("use [" + catalog + "];\n");

                //sb.Append("GRANT VIEW DEFINITION ON [dbo].[Modules] TO [" + userName + "];\n");

               

                //deny all the CUD rigts and Alter right on all the tables, but grant the rest of the rights
                //DataSet ds = DataAccess.DBSchema;
                //foreach (DataRow dr in ds.Tables["_Tables"].Rows)
                //{
                //    DenyUserAccess(userName, dr["TABLE_NAME"].ToString(), SQLUserRight.INSERT, SQLUserRight.UPDATE, SQLUserRight.DELETE, SQLUserRight.ALTER);
                //    GrantUserAccess(userName, dr["TABLE_NAME"].ToString(), SQLUserRight.CONTROL, SQLUserRight.REFERENCES, SQLUserRight.TAKE_OWNERSHIP, SQLUserRight.VIEW_CHANGE_TRACKING, SQLUserRight.VIEW_DEFINITION);
                //}

                //sb = new System.Text.StringBuilder();
                //sb.Append("EXEC sp_addrolemember N'db_owner', N'alex';\n");
                //this.ExecuteStoreCommand(sb.ToString());

            }
            catch (System.Exception ex)
            {
                throw ex;
            }

        }


    }
}
